package zy.dao.vip.set.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.vip.set.VipSetDAO;
import zy.entity.vip.set.T_Vip_AgeGroupSetUp;
import zy.entity.vip.set.T_Vip_BirthdaySms;
import zy.entity.vip.set.T_Vip_Grade;
import zy.entity.vip.set.T_Vip_ReturnSetUp;
import zy.entity.vip.set.T_Vip_Setup;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class VipSetDAOImpl extends BaseDaoImpl implements VipSetDAO {

	@Override
	public T_Vip_BirthdaySms loadBirthdaySms(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vb_id,vb_shop_code,vb_state,vb_agowarn_day,vb_sms_content FROM t_vip_birthdaysms WHERE 1=1 ");
		sql.append(" AND vb_shop_code = :shop_code ");
		sql.append(" AND companyid = :companyid "); 
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), params,new BeanPropertyRowMapper<>(T_Vip_BirthdaySms.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public List<T_Vip_ReturnSetUp> loadReturnSetUp(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT rts_id,rts_day,rts_second,rts_name,rts_shop_code,t.companyid FROM t_vip_returnsetup t ");//回访设置
		sql.append(" JOIN t_base_shop sp ON sp_code = rts_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(" AND rts_shop_code = :shop_code");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND rts_shop_code = :shop_upcode");
		}
		sql.append(" AND t.companyid = :companyid "); 
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_ReturnSetUp.class));
	}
	
	@Override
	public List<T_Vip_ReturnSetUp> loadReturnSetUp(String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT rts_id,rts_day,rts_second,rts_name,rts_shop_code,t.companyid  ");
		sql.append(" FROM t_vip_returnsetup t");
		sql.append(" JOIN t_base_shop sp ON sp_code = rts_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND rts_shop_code = :shop_code");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" ORDER BY rts_second");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Vip_ReturnSetUp.class));
	}

	@Override
	public List<T_Vip_AgeGroupSetUp> loadAgeGroupSetUp(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ags_id,ags_beg_age,ags_end_age,ags_shop_code,t.companyid FROM t_vip_agegroupsetup t ");
		sql.append(" JOIN t_base_shop sp ON sp_code = ags_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(" AND ags_shop_code = :shop_code");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND ags_shop_code = :shop_upcode");
		}
		sql.append(" AND t.companyid = :companyid "); 
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_AgeGroupSetUp.class));
	}

	@Override
	public T_Vip_AgeGroupSetUp loadAgeGroupSetUp(Integer ags_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ags_id,ags_beg_age,ags_end_age,ags_shop_code,t.companyid FROM t_vip_agegroupsetup t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND ags_id = :ags_id "); 
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ags_id", ags_id), 
				new BeanPropertyRowMapper<>(T_Vip_AgeGroupSetUp.class));
	}

	@Override
	public List<T_Vip_Grade> loadGrade(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT gd_id,gd_lower,gd_upper,gd_color,gd_name,gd_remark,gd_code,companyid FROM t_vip_grade t ");//会员等级设置
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.companyid = :companyid "); 
		if(StringUtil.isNotEmpty(searchContent)){
			sql.append(" AND INSTR(gd_name,:searchContent) > 0");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Grade.class));
	}

	@Override
	public T_Vip_Setup loadSetUp(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vs_id,vs_loss_day,vs_consume_day,vs_loyalvip_times,vs_richvip_money,vs_activevip_day,vs_shop_code FROM t_vip_setup t ");
		sql.append(" JOIN t_base_shop sp ON sp_code = vs_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(" AND vs_shop_code = :shop_code");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND vs_shop_code = :shop_upcode");
		}
		sql.append(" AND t.companyid = :companyid "); 
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), params,new BeanPropertyRowMapper<>(T_Vip_Setup.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void updateBirthdaySms(T_Vip_BirthdaySms birthdaySms) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) FROM t_vip_birthdaysms WHERE 1=1 ");
		sql.append(" AND vb_shop_code = :vb_shop_code");
		sql.append(" AND companyid = :companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(birthdaySms), Integer.class);
		if(count == 0){
			sql.setLength(0);
			sql.append("INSERT INTO t_vip_birthdaysms");
			sql.append(" (vb_shop_code,vb_state,vb_agowarn_day,vb_sms_content,companyid)");
			sql.append(" VALUES");
			sql.append(" (:vb_shop_code,:vb_state,:vb_agowarn_day,:vb_sms_content,:companyid)");
		}else {
			sql.setLength(0);
			sql.append(" UPDATE t_vip_birthdaysms");
			sql.append(" SET vb_state=:vb_state");
			sql.append(" ,vb_agowarn_day=:vb_agowarn_day");
			sql.append(" ,vb_sms_content=:vb_sms_content");
			sql.append(" WHERE vb_shop_code=:vb_shop_code");
			sql.append(" AND companyid=:companyid");
		}
		try {
			namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(birthdaySms));
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void updateReturnSet(List<T_Vip_ReturnSetUp> returnSetUps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_vip_returnsetup WHERE rts_shop_code=:rts_shop_code AND companyid=:companyid ");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().
				addValue("rts_shop_code", returnSetUps.get(0).getRts_shop_code())
				.addValue("companyid", returnSetUps.get(0).getCompanyid()));
		
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_returnsetup");
		sql.append(" (rts_day,rts_second,rts_name,rts_shop_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:rts_day,:rts_second,:rts_name,:rts_shop_code,:companyid)");
		try{
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(returnSetUps.toArray()));
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void updateGrade(List<T_Vip_Grade> grades) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_vip_grade WHERE companyid=:companyid ");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource()
				.addValue("companyid", grades.get(0).getCompanyid()));
		
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_grade");
		sql.append(" (gd_lower,gd_upper,gd_color,gd_name,gd_remark,gd_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:gd_lower,:gd_upper,:gd_color,:gd_name,:gd_remark,:gd_code,:companyid)");
		try{
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(grades.toArray()));
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void updateSetup(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String shop_code = (String)params.get(CommonUtil.SHOP_CODE);
		String shop_upcode = (String)params.get(CommonUtil.SHOP_UPCODE);
		T_Vip_Setup setup = (T_Vip_Setup)params.get("setup");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) FROM t_vip_setup t ");
		sql.append(" JOIN t_base_shop sp ON sp_code = vs_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(" AND vs_shop_code = :shop_code");
			setup.setVs_shop_code(shop_code);
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND vs_shop_code = :shop_upcode");
			setup.setVs_shop_code(shop_upcode);
		}
		sql.append(" AND t.companyid = :companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		if(count == 0){
			sql.setLength(0);
			sql.append("INSERT INTO t_vip_setup");
			sql.append(" (vs_loss_day,vs_consume_day,vs_loyalvip_times,vs_richvip_money,vs_shop_code,companyid)");
			sql.append(" VALUES");
			sql.append(" (:vs_loss_day,:vs_consume_day,:vs_loyalvip_times,:vs_richvip_money,:vs_shop_code,:companyid)");
		}else {
			sql.setLength(0);
			sql.append(" UPDATE t_vip_setup");
			sql.append(" SET vs_loss_day=:vs_loss_day");
			sql.append(" ,vs_consume_day=:vs_consume_day");
			sql.append(" ,vs_loyalvip_times=:vs_loyalvip_times");
			sql.append(" ,vs_richvip_money=:vs_richvip_money");
			sql.append(" WHERE vs_shop_code=:vs_shop_code");
			sql.append(" AND companyid=:companyid");
		}
		try{
			namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(setup));
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void delReturnSet(Integer rts_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_vip_returnsetup WHERE rts_id = :rts_id");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("rts_id", rts_id));
	}

	@Override
	public void delAgeGroupSet(Integer ags_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_vip_agegroupsetup WHERE ags_id = :ags_id");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("ags_id", ags_id));
	}

	@Override
	public void saveReturnSet(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_vip_returnsetup");
		sql.append(" (rts_day,rts_second,rts_name,rts_shop_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:rts_day,:rts_second,:rts_name,");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(":shop_code,");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(":shop_upcode,");
		}
		sql.append(":companyid)");
		try{
			namedParameterJdbcTemplate.update(sql.toString(),params);
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void saveAgeGroupSet(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_vip_agegroupsetup");
		sql.append(" (ags_beg_age,ags_end_age,ags_shop_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ags_beg_age,:ags_end_age,");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(":shop_code,");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(":shop_upcode,");
		}
		sql.append(":companyid)");
		try{
			namedParameterJdbcTemplate.update(sql.toString(),params);
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void updateAgeGroupSet(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_vip_agegroupsetup SET ags_beg_age=:ags_beg_age,ags_end_age=:ags_end_age ");
		sql.append(" WHERE ags_id = :ags_id ");
		try{
			namedParameterJdbcTemplate.update(sql.toString(),params);
		} catch (Exception e) {
			throw e;
		}
	}
}
